package tablemanager;

import java.util.Map;

import main.Application;
import sqladapter.SQLAdapter;
import sqladapter.SQLFieldAttributes;
import browser.filter.ANDFilterNode;
import browser.filter.BrowserFilter;
import browser.filter.FilterElement;
import browser.filter.FilterLeaf;
import browser.filter.ORFilterNode;
import exception.queryBuilder.AliasNotFound;
import exception.queryBuilder.AttribNotFound;
import exception.queryBuilder.UndefineKeyType;

public class SQLQueryBuilder
{
	Map<String, SQLFieldAttributes> fields;
	String tableName;
	SQLAdapter adapter;
	
	public SQLQueryBuilder(Map<String, SQLFieldAttributes> fields, String tableName, SQLAdapter adapter)
	{
		this.fields = fields;
		this.tableName = tableName;
		this.adapter = adapter;
	}

	public String buildWhereFromKeys(Map<String, String> keyVals) throws AttribNotFound, UndefineKeyType 
	{
		StringBuilder queryBuilder= new StringBuilder();
		Object[] keysAlliasesArr = keyVals.keySet().toArray();
		int keyAmm = keysAlliasesArr.length;	
		String keyName;
		
		keyName=getFieldNameFromAlias(keysAlliasesArr[0].toString());
		Integer type =getFieldType(keyName);
		if(type.equals(SQLFieldAttributes.TYPE_STRING))
			queryBuilder.append(" " + keyName + "='"+keyVals.get(keysAlliasesArr[0].toString())+"'");
		else if(type.equals(SQLFieldAttributes.TYPE_NUMBER))
			queryBuilder.append(" " + keyName + "="+keyVals.get(keysAlliasesArr[0].toString()));
		else 
			throw new UndefineKeyType(tableName,keyName);		
		for(int i=1;i<keyAmm;++i)
		{
			keyName=getFieldNameFromAlias(keysAlliasesArr[i].toString());
			type =getFieldType(keyName);
			if(type.equals(SQLFieldAttributes.TYPE_STRING))
				queryBuilder.append(" AND " + keyName + "='"+keyVals.get(keysAlliasesArr[i].toString())+"'");
			else if(type.equals(SQLFieldAttributes.TYPE_NUMBER))
				queryBuilder.append(" AND " + keyName + "="+keyVals.get(keysAlliasesArr[i].toString()));
			else 
				throw new UndefineKeyType(tableName,keyName);
			
		}
		///String fieldName = 
		
		return queryBuilder.toString();
	}
	
	/**
	 * 
	 * @param nazwaPola nazwa jak w bazie
	 * @return
	 */
	private String getFunc(String nazwaPola)
	{						
		return fields.get(nazwaPola).getFunc();
	}
	
	/**
	 * zwraca nazwe funkcji jesli jest podpieta
	 * @param nazwaPola nazwa jak w bazie
	 * @return
	 */
	private Integer getFieldType(String nazwaPola)
	{						
		return fields.get(nazwaPola).getFieldType();
	}
	
	/**
	 * 
	 * @param allias allias atrubutu, ktorego nazwa ma byc znaleziona
	 * @return zwraca nazwe z bazy atrybutu pobierajac jego allias
	 * @throws AttribNotFound 
	 * @throws Exception
	 */
	public String getFieldNameFromAlias(String alias) throws AttribNotFound
	{
		Object[] fieldKeys = fields.keySet().toArray();
		for(int i=0;i<fieldKeys.length;++i)
		{
			String currAlias = fields.get(fieldKeys[i]).getFieldAllias();
			if(alias.equals(currAlias))
				return fields.get(fieldKeys[i]).getFieldName();
		}
		
		Map<String,SQLFieldAttributes> views = adapter.getViewFields();
		fieldKeys = views.keySet().toArray(new String[0]);
		for(int i=0;i<fieldKeys.length;++i)
		{
			String currAlias = views.get(fieldKeys[i]).getFieldAllias();
			if(alias.equals(currAlias))
				return views.get(fieldKeys[i]).getFieldName();
		}

		throw new AttribNotFound(tableName,alias);
	}
	
	/**
	 * zwroci alias jak poda sie nazwe z bazy
	 * @param fieldName
	 * @return
	 * @throws AliasNotFound 
	 */
	public String getAliasFromFieldName(String fieldName) throws AliasNotFound 
	{
		Object[] fieldKeys = fields.keySet().toArray();
		for(int i=0;i<fieldKeys.length;++i)
		{
			String currFieldName = fields.get(fieldKeys[i]).getFieldName();
			if(currFieldName.equals(fieldName))
				return fields.get(fieldKeys[i]).getFieldAllias();
		}
		throw new AliasNotFound(tableName,fieldName);
	}
	
	public String getModyfiQuery(Map<String,String> record,Map<String,String> keyVals) throws UndefineKeyType, AttribNotFound 
	{
		replaceStupidChars(record);
		replaceStupidChars(keyVals);
		StringBuilder query= new StringBuilder("");
		query.append(" UPDATE " + tableName+" ");
		query.append(" SET update_login = '" + Application.getInstance().getLogin() + "',");
		Object[] keys = record.keySet().toArray(); 
		boolean addComa = false;
		boolean firstComaSkipped = false;
		for(int i=0;i<keys.length;++i)
		{			
			if(record.get(keys[i])==null||record.get(keys[i]).equals(""))
			{
				addComa=false;
				continue;
			}else
			{
				//TODO przecinek to qrwa comma !!!!
				if(!firstComaSkipped)
				{
					addComa=false;
					firstComaSkipped=true;
				}else
					addComa=true;
			}
			
			//TODO mozna by kiedys kiedys kiedys kiedys dorobic jakies eleganckie sprawdzanie tych funkcji dla zapytan DML <hahaha>. taaaa
			if(addComa)
				query.append(",");
			if(getFieldType(keys[i].toString()).equals(SQLFieldAttributes.TYPE_STRING))				
				query.append(keys[i]+"= " +
					//	getFunc(keys[i].toString())+
						"('" + record.get(keys[i])+"') ");
			else if(getFieldType(keys[i].toString()).equals(SQLFieldAttributes.TYPE_NUMBER))				
				query.append(keys[i]+"= "+
						//getFunc(keys[i].toString())+
						"( " + record.get(keys[i])+") ");
			else 
				throw new UndefineKeyType(tableName,keys[i].toString());
				
		}				
	
		query.append(" WHERE ");
		
		String whereQuery=buildWhereFromKeys(keyVals);
		query.append(whereQuery);
		query.append(";");	
		
		return query.toString();
	}
	
	
	public String getInsertQuery(Map<String,String> record) throws UndefineKeyType
	{
		replaceStupidChars(record);
		StringBuilder query= new StringBuilder("");
		query.append(" INSERT INTO " + tableName+" ");
		query.append(" ( insert_login, ");
		Object[] keys = record.keySet().toArray(); 
		boolean addComa = false;
		boolean firstComaSkipped = false;
		for(int i=0;i<keys.length;++i)
		{
			if(record.get(keys[i]).equals(""))
			{
				addComa=false;
				continue;
			}else
			{
				if(!firstComaSkipped)
				{
					addComa=false;
					firstComaSkipped=true;
				}else
					addComa=true;
			}
				
			if(addComa)
				query.append(",");
			query.append(" " + keys[i]);
		}		
		query.append(" )");
		query.append(" VALUES ( '" + Application.getInstance().getLogin() + "',");
		addComa = false;
		firstComaSkipped = false;
		for(int i=0;i<keys.length;++i)
		{
			if(record.get(keys[i]).equals(""))
			{
				addComa=false;
				continue;
			}
			else
			{
				if(!firstComaSkipped)
				{
					addComa=false;
					firstComaSkipped=true;
				}else
					addComa=true;
			}
				
			if(addComa)
				query.append(",");
			
			query.append(getFunc(keys[i].toString()));
			query.append("(");
			
			if(getFieldType(keys[i].toString()).equals(SQLFieldAttributes.TYPE_STRING))				
				query.append(" '" + record.get(keys[i])+"' ) ");
			else if(getFieldType(keys[i].toString()).equals(SQLFieldAttributes.TYPE_NUMBER))				
				query.append(" " + record.get(keys[i])+" ) ");
			else 
				throw new UndefineKeyType(tableName,keys[i].toString());			
		}				
		query.append(" );");
		return query.toString();
	}
	public String getDeleteQuery(Map<String,String> keyVals) throws AttribNotFound, UndefineKeyType
	{
		replaceStupidChars(keyVals);
		String query= "DELETE FROM " + tableName + " WHERE ";				
		return query + buildWhereFromKeys(keyVals);		
	}
	public String getSelectVisibleQuery(BrowserFilter filter)
	{
		String query = adapter.getSelectVisibleQuery();
		if(filter==null)
			return query;
		String where = buildWhereFromFilter(filter);
		
		return query + " WHERE " + where;
	}

	public String getSelectVisibleQuery(int limitNumber)
	{
		String query = adapter.getSelectVisibleQuery();
		
		return query + " LIMIT " + limitNumber;
	}
		
	/**
	 * przesmiga po drzewie filtru i zbuduje where!!! UUUAAAAA!!!!!!!!!
	 * @param filter
	 * @return
	 */
	private String buildWhereFromFilter(BrowserFilter filter) 
	{
		FilterElement root = filter.getRoot();
		StringBuilder where= new StringBuilder();
		bulidWhereImpl(where,root);
		return where.toString();
	}
		
	private void bulidWhereImpl(StringBuilder where, FilterElement elem) 
	{
		if(elem instanceof FilterLeaf)
		{
			FilterLeaf leaf = (FilterLeaf)elem;
			
			if(leaf.isLikeode())
			{
				where.append(" "+leaf.getLeafName()+" LIKE ");		
				String leafVal = leaf.getLeafValue().replaceAll("\\*", "%"); 
				where.append(" '"+ leafVal+"' " );
			}
			else if(leaf.isInMode())
			{
				where.append(" "+leaf.getLeafName()+" BETWEEN ");		
				where.append(" "+leaf.getLeafValue()+" " );
			}
			else
			{
				where.append(" "+leaf.getLeafName()+" = ");
				if(getFieldType(leaf.getLeafName()).equals(SQLFieldAttributes.TYPE_NUMBER))			
					where.append( leaf.getLeafValue() );
				else				
					where.append(" '"+ leaf.getLeafValue()+"' " );
		
			}
		}
		else if(elem instanceof ANDFilterNode)
		{
			ANDFilterNode node = (ANDFilterNode)elem;
			
			for (int i=0;i<node.getFilterElements().size();++i ) 
			{
				FilterElement nodeElem =node.getFilterElements().get(i);
				if(i!=0)
					where.append(" AND ");
				
				where.append(" (");
				bulidWhereImpl(where,nodeElem);
				where.append(" ) ");				
			}
		}
		else if(elem instanceof ORFilterNode)
		{
			ORFilterNode node = (ORFilterNode)elem;
			
			for (int i=0;i<node.getFilterElements().size();++i ) 
			{
				FilterElement nodeElem =node.getFilterElements().get(i);
				if(i!=0)
					where.append(" OR ");
				
				where.append(" (");
				bulidWhereImpl(where,nodeElem);
				where.append(" ) ");				
			}
		}
	}
	
	/**
	 * metoda sprawdzi,czy zapodane pole jest w tabeli/widoku
	 * @param field
	 * @return
	 */
	public boolean validField(String field)
	{
		boolean validView = (adapter.getViewFields())!=null && (adapter.getViewFields().get(field)!=null);
		if(validView)
			return true;
		boolean validTable = (fields)!=null && (fields.get(field)!=null);
		return validTable;
	}
	
	public String getSelectAllQuery()
	{
		String query = adapter.getSelectAllQuery();
		return query;
	}
	public String getSelectFromKeys(Map<String,String> keys) throws AttribNotFound, UndefineKeyType 
	{
		String query;
		query =getSelectAllQuery();
		query += " WHERE"+ buildWhereFromKeys(keys) + ";";
		return query;
	}
	/**
	 * zaspapi \ ; ' \\ tak,zeby bylo dobrze
	 * @param in
	 * @return
	 */
	private String replaceStupidChars(String in)
	{
		in = in.replace("\\", "\\\\");
		in = in.replace(";", "\\;");
		in = in.replace("'", "\\'");
		String out = in.replace("\"", "\\\"");
		return out;
	}
	/**
	 * zaspapi \ ; ' \\ tak,zeby bylo dobrze. Zrobi to we wszystkich wartosciach zapodanej mapy
	 * @param in
	 * @return
	 */
	private void replaceStupidChars(Map<String,String> in)
	{
		String[] keys = in.keySet().toArray(new String[0]);
		for(int i=0;i<keys.length;++i)
		{
			String val = in.get(keys[i]);
			if(val==null)
				continue;
			val = replaceStupidChars(val);
			in.put(keys[i], val);
		}		
	}
}
